from fastapi import APIRouter, Query, Depends
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

air_report_router = APIRouter(prefix="/report", tags=["空气质量管理"], dependencies=[Depends(oauth2_scheme)])


class AirSearch(BaseModel):
    fsiteNo: str = Field(None, description="站点编号")
    date: str = Field(None, description="时间")


@air_report_router.get("/airreport001", summary="每日空气质量")
def air_report_001(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport001( :site_no , :date );"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/airreport002", summary="每日空气质量")
def air_report_002(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport002( :site_no , :date );"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/airreport003", summary="每月空气质量")
def air_report_003(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport003( :site_no , :date );"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/airreport004", summary="每月空气质量")
def air_report_004(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport004( :site_no , :date );"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date
    }).fetchall()
    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


class AirSearch2(BaseModel):
    fsiteNo: str = Field(None, description="站点编号")
    year: str = Field(None, description="年份")
    season: str = Field(None, description="月份")


@air_report_router.get("/airreport005", summary="每季空气质量")
def air_report_005(search: AirSearch2 = Query(), db: Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport005( :site_no , :date , :season);"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.year,
        "season": search.season,
    })

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/airreport006", summary="每季空气质量")
def air_report_006(search: AirSearch2 = Query(), db: Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport006( :site_no , :date , :season );"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.year,
        "season": search.season,
    })

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/airreport007", summary="每年空气质量")
def air_report_007(search: AirSearch2 = Query(), db: Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport007( :site_no , :date );"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.year,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/airreport008", summary="每年空气质量")
def air_report_008(search: AirSearch2 = Query(), db: Session = Depends(get_db)):
    sql = "select * from proc_rpt_airreport008( :site_no , :date );"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.year,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fentryid'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['faqi1'] = data[key][3]
        d1['ftype1'] = data[key][4]
        d1['fmain1'] = data[key][5]
        d1['faqi2'] = data[key][6]
        d1['ftype2'] = data[key][7]
        d1['fmain2'] = data[key][8]
        d1['faqi3'] = data[key][9]
        d1['ftype3'] = data[key][10]
        d1['fmain3'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/getDayAirDataByFsiteNo", summary="每日空气- 室内 按照学校")
def get_dayAirDataByFsiteNo(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select date_part('hour',fmoniter_date) fhour,fmoniter_pm26 pm25,fmoniter_pm11 pm10,fmoniter_co2 co2,fmoniter_yjw TVOC,fmoniter_jq CH2O,fsd2 H,fwd2 T"
    sql = sql + " ,case faqi when 1 then '优' when 2 then '良' when 3 then '轻度' when 4 then '中度' when 5 then '重度' else '未知' end zhibiao"
    sql = sql + " from A_TC_SITE_MONITER"
    sql = sql + " where 1=1"
    sql = sql + " and fsite_no= :site_no "
    sql = sql + " and to_char(fmoniter_date,'yyyy-mm-dd')= :date "
    sql = sql + " order by 1 ;"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['ch2O'] = data[key][5]
        d1['co'] = None
        d1['co2'] = data[key][3]
        d1['faqi2'] = None
        d1['fhour'] = data[key][0]
        d1['fmoniterDate'] = None
        d1['fmoniterMonth'] = None
        d1['fpointFlag'] = None
        d1['h'] = data[key][6]
        d1['no2'] = None
        d1['o3'] = None
        d1['pm10'] = data[key][2]
        d1['pm25'] = data[key][1]
        d1['so2'] = None
        d1['t'] = data[key][7]
        d1['tvoc'] = data[key][4]
        d1['zhibiao'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/getDayAirDataByFpointNo", summary="每日空气 室内-按楼层")
def get_air_report102(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select date_part('hour',fmoniter_date) fhour,fmoniter_pm25 pm25,fmoniter_pm10 pm10,fmoniter_co2 co2,fmoniter_yjw TVOC,fmoniter_jq CH2O,fsd H,fwd T"
    sql = sql + " ,case faqi when 1 then '优' when 2 then '良' when 3 then '轻度' when 4 then '中度' when 5 then '重度' else '未知' end zhibiao"
    sql = sql + " from A_TC_POINT_MONITER"
    sql = sql + " where 1=1"
    sql = sql + " and fpoint_no= :site_no "
    sql = sql + " and to_char(fmoniter_date,'yyyy-mm-dd')= :date "
    sql = sql + " order by 1 ;"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['ch2O'] = data[key][5]
        d1['co'] = None
        d1['co2'] = data[key][3]
        d1['faqi2'] = None
        d1['fhour'] = data[key][0]
        d1['fmoniterDate'] = None
        d1['fmoniterMonth'] = None
        d1['fpointFlag'] = None
        d1['h'] = data[key][6]
        d1['no2'] = None
        d1['o3'] = None
        d1['pm10'] = data[key][2]
        d1['pm25'] = data[key][1]
        d1['so2'] = None
        d1['t'] = data[key][7]
        d1['tvoc'] = data[key][4]
        d1['zhibiao'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/getDayAirDataByFpointNo2", summary="每日空气 室内-按教室")
def get_air_report103(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select date_part('hour',fmoniter_date) fhour,fmoniter_pm25 pm25,fmoniter_pm10 pm10,fmoniter_co2 co2,fmoniter_yjw TVOC,fmoniter_jq CH2O,fsd2 H,fwd2 T"
    sql = sql + " ,case faqi when 1 then '优' when 2 then '良' when 3 then '轻度' when 4 then '中度' when 5 then '重度' else '未知' end zhibiao"
    sql = sql + " from A_TN_POINT_MONITER"
    sql = sql + " where 1=1"
    sql = sql + " and fpoint_no= :site_no "
    sql = sql + " and to_char(fmoniter_date,'yyyy-mm-dd')= :date "
    sql = sql + " order by 1 ;"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['ch2O'] = data[key][5]
        d1['co'] = None
        d1['co2'] = data[key][3]
        d1['faqi2'] = None
        d1['fhour'] = data[key][0]
        d1['fmoniterDate'] = None
        d1['fmoniterMonth'] = None
        d1['fpointFlag'] = None
        d1['h'] = data[key][6]
        d1['no2'] = None
        d1['o3'] = None
        d1['pm10'] = data[key][2]
        d1['pm25'] = data[key][1]
        d1['so2'] = None
        d1['t'] = data[key][7]
        d1['tvoc'] = data[key][4]
        d1['zhibiao'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/getMonthAirDataByFsiteNo", summary="每月空气 室内 按学校")
def get_air_report201(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select fmoniter_date,fmoniter_pm26 pm25,fmoniter_pm11 pm10,fmoniter_co2 co2,fmoniter_yjw TVOC,fmoniter_jq CH2O,fsd2 H,fwd2 T"
    sql = sql + " ,case faqi when 1 then '优' when 2 then '良' when 3 then '轻度' when 4 then '中度' when 5 then '重度' else '未知' end zhibiao"
    sql = sql + ",faqi2"
    sql = sql + " from A_TD_SITE_MONITER"
    sql = sql + " where 1=1"
    sql = sql + " and fsite_no= :site_no "
    sql = sql + " and to_char(fmoniter_date,'yyyymm')= :date "
    sql = sql + " order by 1 ;"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['ch2O'] = data[key][5]
        d1['co'] = None
        d1['co2'] = data[key][3]
        d1['faqi2'] = data[key][9]
        d1['fhour'] = None
        d1['fmoniterDate'] = data[key][0]
        d1['fmoniterMonth'] = None
        d1['fpointFlag'] = None
        d1['h'] = data[key][6]
        d1['no2'] = None
        d1['o3'] = None
        d1['pm10'] = data[key][2]
        d1['pm25'] = data[key][1]
        d1['so2'] = None
        d1['t'] = data[key][7]
        d1['tvoc'] = data[key][4]
        d1['zhibiao'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/getMonthAirDataByFpointNo", summary="每月空气 室内-按楼层")
def get_air_report202(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select fmoniter_date,fmoniter_pm25 pm25,fmoniter_pm10 pm10,fmoniter_co2 co2,fmoniter_yjw TVOC,fmoniter_jq CH2O,fsd H,fwd T"
    sql = sql + " ,case faqi when 1 then '优' when 2 then '良' when 3 then '轻度' when 4 then '中度' when 5 then '重度' else '未知' end zhibiao"
    sql = sql + ",faqi2"
    sql = sql + " from A_TD_POINT_MONITER"
    sql = sql + " where 1=1"
    sql = sql + " and fpoint_no= :site_no "
    sql = sql + " and to_char(fmoniter_date,'yyyymm')= :date "
    sql = sql + " order by 1 ;"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['ch2O'] = data[key][5]
        d1['co'] = None
        d1['co2'] = data[key][3]
        d1['faqi2'] = data[key][9]
        d1['fhour'] = None
        d1['fmoniterDate'] = data[key][0]
        d1['fmoniterMonth'] = None
        d1['fpointFlag'] = None
        d1['h'] = data[key][6]
        d1['no2'] = None
        d1['o3'] = None
        d1['pm10'] = data[key][2]
        d1['pm25'] = data[key][1]
        d1['so2'] = None
        d1['t'] = data[key][7]
        d1['tvoc'] = data[key][4]
        d1['zhibiao'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/getYearAirDataByFsiteNo", summary="每年空气 室内--按学校")
def get_air_report301(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select fmoniter_month,fmoniter_pm26 pm25,fmoniter_pm11 pm10,fmoniter_co2 co2,fmoniter_yjw TVOC,fmoniter_jq CH2O,fsd2 H,fwd2 T"
    sql = sql + " ,case faqi when 1 then '优' when 2 then '良' when 3 then '轻度' when 4 then '中度' when 5 then '重度' else '未知' end zhibiao"
    sql = sql + ",faqi2"
    sql = sql + " from A_TM_SITE_MONITER"
    sql = sql + " where 1=1"
    sql = sql + " and fsite_no= :site_no "
    sql = sql + " and left(fmoniter_month,4)= :date "
    sql = sql + " order by 1 ;"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['ch2O'] = data[key][5]
        d1['co'] = None
        d1['co2'] = data[key][3]
        d1['faqi2'] = data[key][9]
        d1['fhour'] = None
        d1['fmoniterDate'] = None
        d1['fmoniterMonth'] = data[key][0]
        d1['fpointFlag'] = None
        d1['h'] = data[key][6]
        d1['no2'] = None
        d1['o3'] = None
        d1['pm10'] = data[key][2]
        d1['pm25'] = data[key][1]
        d1['so2'] = None
        d1['t'] = data[key][7]
        d1['tvoc'] = data[key][4]
        d1['zhibiao'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)


@air_report_router.get("/getYearAirDataByFpointNo", summary="每年空气 室内--按楼层")
def get_air_report302(search: AirSearch = Query(), db: Session = Depends(get_db)):
    sql = "select fmoniter_month,fmoniter_pm25 pm25,fmoniter_pm10 pm10,fmoniter_co2 co2,fmoniter_yjw TVOC,fmoniter_jq CH2O,fsd H,fwd T"
    sql = sql + " ,case faqi2 when 1 then '优' when 2 then '良' when 3 then '轻度' when 4 then '中度' when 5 then '重度' else '未知' end zhibiao"
    sql = sql + ",faqi2"
    sql = sql + " from A_TM_POINT_MONITER"
    sql = sql + " where 1=1"
    sql = sql + " and fpoint_no= :site_no "
    sql = sql + " and left(fmoniter_month,4)= :date "
    sql = sql + " order by 1 ;"
    data = db.execute(text(sql), {
        "site_no": search.fsiteNo,
        "date": search.date,
    }).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['ch2O'] = data[key][5]
        d1['co'] = None
        d1['co2'] = data[key][3]
        d1['faqi2'] = data[key][9]
        d1['fhour'] = None
        d1['fmoniterDate'] = None
        d1['fmoniterMonth'] = data[key][0]
        d1['fpointFlag'] = None
        d1['h'] = data[key][6]
        d1['no2'] = None
        d1['o3'] = None
        d1['pm10'] = data[key][2]
        d1['pm25'] = data[key][1]
        d1['so2'] = None
        d1['t'] = data[key][7]
        d1['tvoc'] = data[key][4]
        d1['zhibiao'] = data[key][8]
        data_list.append(d1)

    info_dict = {"list": data_list}
    return BaseResponse.success(data=info_dict)
